package dao.customer.impl;

import dao.BaseDao;
import dao.customer.CustomerDao;
import pojo.*;
import util.PageSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 作者：孩子他爹  日期2019/4/10
 * 顾客操作实现类
 */
public class CustomerDaoImpl extends BaseDao implements CustomerDao {

    ResultSet rs;
    @Override
    public int addCustomerDao(Customer cto) {
        if(customerLoginNameExists(cto.getCusLoginName())>0)return -1;  //判断注册的登录名是否存在
        int irt=0;
        String sql="INSERT INTO customer(`cusName`,`cusLoginName`,`cusPassword`,`" +
                "cusEmail`,`cusSex`,`cusPhoto`,`cusHobby`,`cusCode`,`cusBirthday`)" +
                " VALUES(?,?,?,?,?,?,?,?,?);";
        Object [] params ={cto.getCusName(),cto.getCusLoginName(),cto.getCusPassword(),
                            cto.getCusEmail(),cto.getCusSex(),cto.getCusPhoto(),cto.getCusHobby(),cto.getCusCode(),
                            cto.getCusBirthday()};
        irt=this.executeUpdateRows(sql,params);
        return irt;
    }

    @Override
    public int customerLoginNameExists(String name) {
        int ire=0;
        String sql="SELECT COUNT(*) FROM `customer` WHERE `cusLoginName`=?;";
        Object [] params={name};
        rs=this.executeSelect(sql,params);
        try {
            if(rs.next())ire=rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null)rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return ire;
    }

    @Override
    public Customer customerExists(String name, String pwd) {
        Customer ire = new Customer();
        String sql="SELECT * FROM `customer` WHERE `cusLoginName`=? and `cusPassword`=?";
        Object [] params={name,pwd};
        ResultSet rs=this.executeSelect(sql,params);
        try {
            if(rs.next()){
                ire.setId(rs.getInt(1));
                ire.setCusName(rs.getString(2));
                ire.setCusLoginName(rs.getString(3));
                ire.setCusPassword(rs.getString(4));
                ire.setCusEmail(rs.getString(5));
                ire.setCusSex(rs.getString(6));
                ire.setCusPhoto(rs.getString(7));
                ire.setCusHobby(rs.getString(8));
                ire.setCusCode(rs.getString(9));
                ire.setCusBirthday(rs.getTimestamp(10));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null)rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return ire;
    }

    @Override
    public List<BigClass> getBigClass() {
        List<BigClass> list=new ArrayList<>();
        String sql="SELECT * FROM `bigclass`;";
        Object [] params={};
        BigClass bigClass;
        rs=this.executeSelect(sql,params);
        try {
            while (rs.next()){
                bigClass=new BigClass();
                bigClass.setBigId(rs.getInt(1));
                bigClass.setBigName(rs.getString(2));
                bigClass.setBigText(rs.getString(3));
                list.add(bigClass);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null)rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<Smallclass> getSmallclassById(long smallBigId) {
        List<Smallclass> list=new ArrayList<>();
        String sql="SELECT * FROM `smallclass` WHERE `smallBigId`=?;";
        Object [] params={smallBigId};
        Smallclass smallclass;
        rs=this.executeSelect(sql,params);
        try {
            while (rs.next()){
                smallclass=new Smallclass();
                smallclass.setSmallId(rs.getLong(1));
                smallclass.setSmallName(rs.getString(2));
                smallclass.setSmallBigId(rs.getLong(3));
                smallclass.setSmallText(rs.getString(4));
                list.add(smallclass);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null)rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<ShoppingRecord> getShoppingRecordByCusId(long id, long scStatus) {
        if(!(scStatus==1 || scStatus==0)){
            scStatus=1;
        }
        List<ShoppingRecord> list=new ArrayList<>();
        String sql="SELECT s.*,g.`goodsName`,g.`goodsImage`,g.`goodsMoney`,ROUND((g.`goodsMoney`*d.`discRate`),2) AS zk, " +
                "d.`discRate`,g.`id`,g.`goodsCarriage` FROM shoppingcar AS s  " +
                "INNER JOIN `goods` AS g ON s.`scGoodsId`=g.`id` " +
                "INNER JOIN `discount` AS d ON g.`goodsDiscId`=d.id WHERE scCusId=? AND scStatus=?;";
        Object [] par={id,scStatus};
        //System.out.println(sql);
        ResultSet rs=this.executeSelect(sql,par);
        try {
            ShoppingRecord shoppingRecord;
            while (rs.next()){
                shoppingRecord=new ShoppingRecord();
                shoppingRecord.setId(rs.getLong(1));
                shoppingRecord.setScCusId(rs.getLong(2));
                shoppingRecord.setScGoodsId(rs.getLong(3));
                shoppingRecord.setScNumber(rs.getLong(4));
                shoppingRecord.setScStatus(rs.getLong(5));
                shoppingRecord.setGoodsName(rs.getString(6));
                shoppingRecord.setGoodsImage(rs.getString(7));
                shoppingRecord.setGoodsMoney(rs.getDouble(8));
                shoppingRecord.setDiscountPrice(rs.getDouble(9));
                shoppingRecord.setDiscRate(rs.getDouble(10));
                shoppingRecord.setGoodsId(rs.getLong(11));
                shoppingRecord.setGoodsCarriage(rs.getDouble(12));
               list.add(shoppingRecord);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<ShoppingRecord> getShoppingRecordByCusId(long id, long scStatus, PageSupport ps) {
        if(!(scStatus==1 || scStatus==0)){
            scStatus=1;
        }
        int index=(ps.getCurrentPage()-1)*ps.getPageSize();
        List<ShoppingRecord> list=new ArrayList<>();
        String sql="SELECT s.*,g.`goodsName`,g.`goodsImage`,g.`goodsMoney`,ROUND((g.`goodsMoney`*d.`discRate`),2) AS zk, " +
                "d.`discRate`,g.`id`,g.`goodsCarriage` FROM shoppingcar AS s  " +
                "INNER JOIN `goods` AS g ON s.`scGoodsId`=g.`id` " +
                "INNER JOIN `discount` AS d ON g.`goodsDiscId`=d.id WHERE scCusId=? AND scStatus=? LIMIT ?,?;";
        Object [] par={id,scStatus,index,ps.getPageSize()};
        //System.out.println(sql);
        ResultSet rs=this.executeSelect(sql,par);
        try {
            ShoppingRecord shoppingRecord;
            while (rs.next()){
                shoppingRecord=new ShoppingRecord();
                shoppingRecord.setId(rs.getLong(1));
                shoppingRecord.setScCusId(rs.getLong(2));
                shoppingRecord.setScGoodsId(rs.getLong(3));
                shoppingRecord.setScNumber(rs.getLong(4));
                shoppingRecord.setScStatus(rs.getLong(5));
                shoppingRecord.setGoodsName(rs.getString(6));
                shoppingRecord.setGoodsImage(rs.getString(7));
                shoppingRecord.setGoodsMoney(rs.getDouble(8));
                shoppingRecord.setDiscountPrice(rs.getDouble(9));
                shoppingRecord.setDiscRate(rs.getDouble(10));
                shoppingRecord.setGoodsId(rs.getLong(11));
                shoppingRecord.setGoodsCarriage(rs.getDouble(12));
                list.add(shoppingRecord);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int deleteShoppingRecord(long srId) {
        int ire=0;
        String sql="DELETE FROM `shoppingcar` WHERE id=?;";
        Object [] params={srId};
        ire=this.executeUpdateRows(sql,params);
        return ire;
    }

    @Override
    public int updateShop(long srId,int number) {
        int ire=0;
        String sql="UPDATE `shoppingcar` SET scNumber=? WHERE id=? AND scStatus=1;;";
        Object [] params={number,srId};
        ire=this.executeUpdateRows(sql,params);
        return ire;
    }

    @Override
    public int getShopCount(long srId,long statuc) {
        int ire=0;
        String sql="SELECT COUNT(*) FROM `shoppingcar` WHERE scCusId=? AND scStatus=?;;";
        Object [] par={srId,statuc};
        ResultSet rs=this.executeSelect(sql,par);
        try {
            if (rs.next()){
                ire=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ire;
    }

    @Override
    public int addShopRec(List<ShoppingRecord> lsr,String address) {
        int ire=0;
        String sql="INSERT INTO `orderse`(`orderseGoodsId`,`orderseCusId`,`orderseDate`,`orderseAddress`,`orderseMoney`)" +
                " VALUES(?,?,?,?,?)";
        //`orderseGoodsId`,`orderseCusId`,`orderseDate`,`orderseAddress`,`orderseMoney`,`orderseStatus`
        String sqlShop="UPDATE `shoppingcar` SET scStatus=0 WHERE id=?;";
        Object [] pam={};
        for (int i = 0; i < lsr.size(); i++) {
            pam=new Object[]{lsr.get(i).getGoodsId(),lsr.get(i).getScCusId(),new Date(),address,lsr.get(i).getDiscountPrice()};
            if(this.executeUpdateRows(sqlShop,new Object[]{lsr.get(i).getId()})>0){
                ire+=this.executeUpdateRows(sql,pam);
            }else {
                return ire;
            }
        }
        return ire;
    }

    @Override
    public List<CusOrderse> getOrderseList(Long cid,int index,int row) {
        List<CusOrderse> ordList=new ArrayList<>();
        String sql="SELECT r.`id`,g.`goodsName`,c.`cusName`,r.`orderseDate`,r.`orderseAddress`,r.`orderseMoney`,r.`orderseStatus` FROM `orderse` AS r " +
                "INNER JOIN `goods` AS g ON r.`orderseGoodsId`=g.`id` " +
                "INNER JOIN `customer` AS c ON r.`orderseCusId`=c.`id` WHERE r.orderseCusId=? and `orderseStatus`=0 LIMIT ?,?;";
        Object [] pam={cid,(index-1)*row,row};
        ResultSet rs=this.executeSelect(sql,pam);
        try {
            CusOrderse ord;
            //`orderseGoodsId`,`orderseCusId`,`orderseDate`,`orderseAddress`,`orderseMoney`,`orderseStatus`
            while (rs.next()) {
                ord=new CusOrderse();
                ord.setId(rs.getLong(1));
                ord.setOrderseGoodsName(rs.getString(2));
                ord.setOrderseCusName(rs.getString(3));
                ord.setOrderseDate(rs.getTimestamp(4));
                ord.setOrderseAddress(rs.getString(5));
                ord.setOrderseMoney(rs.getDouble(6));
                ord.setOrderseStatus(rs.getLong(7));
                ordList.add(ord);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null)rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return ordList;
    }

    @Override
    public int getOrderseCount(Long cid) {
        int count=0;
        String sql="SELECT COUNT(*) FROM `orderse` AS r " +
                "INNER JOIN `goods` AS g ON r.`orderseGoodsId`=g.`id` " +
                "INNER JOIN `customer` AS c ON r.`orderseCusId`=c.`id` WHERE r.orderseCusId=? AND r.orderseStatus=0;";
        Object [] pam={cid};
        ResultSet rs=this.executeSelect(sql,pam);
        try {
            if(rs.next())count=rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null)rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }

    @Override
    public int addShopGods(Shoppingcar shop) {
        int ire=0;
        String sql="INSERT INTO `shoppingcar`(`scCusId`,`scGoodsId`,`scNumber`,`scStatus`) " +
                "VALUES(?,?,?,?);";
        Object [] pam={shop.getScCusId(),shop.getScGoodsId(),shop.getScNumber(),shop.getScStatus()};
        ire=this.executeUpdateRows(sql,pam);
        return ire;
    }

    @Override
    public Customer getCusByCodeName(String code, String name) {
        Customer customer=null;
        String sql="SELECT * FROM `customer` WHERE `cusCode`=? AND `cusName`=?";
        ResultSet rs=this.executeSelect(sql,new Object[]{code,name});
        try {
            if (rs.next()){
                customer=new Customer();
                //`id``cusName``cusLoginName``cusPassword``cusEmail``cusSex``cusPhoto``cusHobby``cusCode``cusBirthday`
                customer.setId(rs.getLong(1));
                customer.setCusName(rs.getString(2));
                customer.setCusLoginName(rs.getString(3));
                customer.setCusPassword(rs.getString(4));
                customer.setCusEmail(rs.getString(5));
                customer.setCusSex(rs.getString(6));
                customer.setCusPhoto(rs.getString(7));
                customer.setCusHobby(rs.getString(8));
                customer.setCusCode(rs.getString(9));
                customer.setCusBirthday(rs.getTimestamp(10));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null)rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return customer;
    }

    @Override
    public int updataCuPwd(Long id,String pwd) {
        int ire=0;
        String sql="UPDATE `customer` SET `cusPassword`=? WHERE `id`=?";
        ire=this.executeUpdateRows(sql,new Object[]{pwd,id});
        return ire;
    }

    @Override
    public int updateOrd(Long id) {
        int ire=0;
        String sql="UPDATE `orderse` SET `orderseStatus`=2 WHERE `id`=?;";
        ire=this.executeUpdateRows(sql,new Object[]{id});
        return ire;
    }
}
